﻿using System;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for ICPUserTable
    /// </summary>
    public class ICPUserTable : CacheAbstraction
    {
        public UserData GetUserData(String sxUserId)
        {
            return GetUserData(sxUserId, false);
        }
        public UserData GetUserData(String sxUserId, bool bForceRefresh)
        {
            UserData ud = new UserData();
            ud.ID = sxUserId;

            DataRow[] dr = GetUserList(bForceRefresh).Select(String.Format("ID = '{0}'", sxUserId));

            if (dr.Length > 0)
            {
                ud.sxName = dr[0].ReadString("NAME");
                ud.sxEmailAddress = dr[0].ReadString("E_MAIL_ADDR");
                ud.sxJobPosition = dr[0].ReadString("JOB_POSITION_CODE");
                ud.sxOrganization = dr[0].ReadString("ORGANIZATION_CODE");
            }

            DataTable dtInfos = GetUserProfileDataList(sxUserId, bForceRefresh);

            foreach (DataRow dr2 in dtInfos.Rows)
            {
                String sxKey = Convert.ToString(dr2["P_USER_PROFILE_DATA_TYPE_ID"]);
                String sxValue = Convert.ToString(dr2["VALUE"]);

                ud.AddCustomData(sxKey, sxValue);
            }

            return ud;
        }
        public DataTable GetUserList()
        {
            return GetUserList(false);
        }
        public DataTable GetUserList(bool bForceRefresh)
        {
            String sxCacheKey = KCache.ICP_USER;

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                    @"
                    select
                        ICP_USER_ID ID,
                        EMPLOYEE_NAME NAME,
                        ORGANIZATION_CODE,
                        E_MAIL_ADDR,
                        PROFIT_CODE,
                        JOB_POSITION_CODE
                    from 
                        V_ICP_USER
                    order by
                        EMPLOYEE_NAME ASC
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery);

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetUserProfileDataList(String sxUserId)
        {
            return GetUserProfileDataList(sxUserId, false);
        }
        public DataTable GetUserProfileDataList(String sxUserId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.V_USER_PROFILE_DATA + sxUserId;

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                OracleParameter param = new OracleParameter();
                param.ParameterName = "prmId";
                param.OracleDbType = OracleDbType.VarChar;
                param.Value = sxUserId;

                String sxQuery =
                    @"
                    select 
                        t.T_USER_PROFILE_DATA_ID ID, 
                        t.ICP_USER_ID, 
                        t.VALUE, 
                        t.P_USER_PROFILE_DATA_TYPE_ID, 
                        p.CODE USER_DATA_TYPE,
                        p.P_INPUT_CONTROL_TYPE_ID CONTROL_TYPE_ID
                    from 
                        T_USER_PROFILE_DATA t
                        left join P_USER_PROFILE_DATA_TYPE p
                            on t.P_USER_PROFILE_DATA_TYPE_ID = p.P_USER_PROFILE_DATA_TYPE_ID
                    where
                        t.ICP_USER_ID = :prmId
                    order by
                        p.CODE ASC";

                DataTable dt = dbl.GetDataTable(sxQuery, param);

                HttpRuntime.Cache.Add(
                    sxCacheKey,
                    dt,
                    CreateDependency(),
                    Cache.NoAbsoluteExpiration,
                    TimeSpan.FromMinutes(5),
                    CacheItemPriority.Normal,
                    null);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public override String GetCacheFilename()
        {
            return KCache.Filename.USER_LIST;
        }
    }
}